from sqlalchemy import create_engine,text,Column,Integer,String
from sqlalchemy.orm import declarative_base,sessionmaker

#数据库的对象
HOST = '127.0.0.1'
PORT = 3306
DATABASE = 'itbz'
USER = 'root'
PWD = 'root'
DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATABASE}'

#创建引擎
engine = create_engine(DB_URI)
#创建一个基类
Base = declarative_base()

class Person(Base):
    __tablename__ = 't_person'
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32))
    age =Column(Integer)
    country = Column(String(32))

#创建session对象来操作数据
Session = sessionmaker(engine)

def create_data_one():
    with Session() as session:
        p1 = Person(name='百战',age=6,country='北京')
        session.add(p1)
        session.commit()

def create_data_all():
    with Session() as session:
        p1 = Person(name='吕布',age=13,country='三国')
        p2 = Person(name='貂蝉',age=15,country='三国')
        session.add_all([p1,p2])
        session.commit()

def query_data_all():
     with Session() as session:
         all_person = session.query(Person).all()
         for p in all_person:
             print(p)
             print(p.name)

def query_data_one():
    with Session() as session:
        p1 = session.query(Person).first()
        print(p1)
        print(p1.name)

def query_data_by_params():
    with Session() as session:
        # p1 = session.query(Person).filter_by(name='吕布').first()
        p1 = session.query(Person).filter(Person.name == '吕布').first()
        print(p1.age)

def update_data():
    with Session() as session:
        p1 = session.query(Person).filter(Person.name == '吕布').first()
        p1.age = 20
        session.commit()

def delete_data():
    with Session() as session:
        p1 = session.query(Person).filter(Person.name == '百战').first()
        session.delete(p1)
        session.commit()

if __name__ == '__main__':
    # create_data_one()
    # create_data_all()
    # query_data_all()
    # query_data_one()
    # query_data_by_params()
    # update_data()
    delete_data()